package com.harmontronics.hanma.paas.paas.domain.app.service.dialect;

import cn.hutool.core.util.PageUtil;
import com.harmontronics.hanma.paas.paas.common.PageBase;
import com.harmontronics.hanma.paas.paas.domain.app.entity.HmEntityModel;
import com.harmontronics.hanma.paas.paas.domain.app.entity.valueobject.ViewDataBaseTypeEnum;
import com.harmontronics.hanma.paas.paas.domain.app.repository.persistence.HmDatasourceEnvRepository;
import com.harmontronics.hanma.paas.paas.domain.app.repository.po.HmDatasourceEnvPO;
import com.harmontronics.hanma.paas.paas.domain.app.repository.po.HmModelPO;
import com.harmontronics.hanma.paas.paas.domain.app.service.HmPublishedEntityModelService;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.util.Pair;

import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.StringJoiner;

/**
 * @author ldb
 */
public abstract class AbstractDialectService implements InitializingBean, DialectQuery {


    private static final String URL_TEMPLATE = "jdbc:%s://%s:%s/%s";

    private static final String LIST_TEMPLATE = "SELECT %s FROM %s WHERE %s %s";
    private static final String PAGE_LIST_TEMPLATE = "SELECT %s FROM %s WHERE %s %s OFFSET %s LIMIT %s";


    private static final String COUNT_TEMPLATE = "SELECT COUNT(*) FROM %s WHERE %s";


    @Value("${app.env:DEV}")
    private String env;


    public abstract HmDatasourceEnvRepository getHmDatasourceEnvRepository();

    public abstract HmPublishedEntityModelService getHmPublishedEntityModelService();

    protected String concatUrl(String code, String host, Integer port, String dbName) {
        return String.format(URL_TEMPLATE, code, host, port.toString(), dbName);
    }

    public abstract ViewDataBaseTypeEnum getDataBaseType();


    public Pair<String, Connection> getConnection(Long datasourceId) throws SQLException {
        List<HmDatasourceEnvPO> pos = getHmDatasourceEnvRepository().findByDatasourceId(datasourceId);
        HmDatasourceEnvPO po = pos.stream().filter(e -> e.getEnv().name().equalsIgnoreCase(env)).findFirst().get();
        String url = concatUrl(getDataBaseType().getCode(), po.getHost(), po.getPort(), po.getDatabaseName());
        return Pair.of(po.getDatabaseName(), DriverManager.getConnection(url, po.getUsername(), po.getPassword()));
    }


    public HikariDataSource createDatasource(Long datasourceId){
        List<HmDatasourceEnvPO> pos = getHmDatasourceEnvRepository().findByDatasourceId(datasourceId);
        HmDatasourceEnvPO po = pos.stream().filter(e -> e.getEnv().name().equalsIgnoreCase(env)).findFirst().get();
        String url = concatUrl(getDataBaseType().getCode(), po.getHost(), po.getPort(), po.getDatabaseName());
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setDriverClassName(getDataBaseType().getDriver());
        config.setUsername(po.getUsername());
        config.setPassword(po.getPassword());
        config.setMinimumIdle(0);
        config.setMaximumPoolSize(2);
        return new HikariDataSource(config);
    }


    public abstract List<ViewColumn> getViewColumns(Long datasourceId, String viewName);


    @Override
    public List<Map<String, Object>> list(HmModelPO modelPO, Map<String, Object> params, List<String> orderRules) throws SQLException {

        String sql = applyListSql(modelPO, params, orderRules);
        Pair<String, Connection> pair = getConnection(modelPO.getDatasourceId());
        return  getData(pair, sql,true);

    }

    private List<Map<String, Object>> getData(Pair<String, Connection> pair, String sql,boolean autoClose) throws SQLException {

        Connection connection = null;
        try {
            connection = pair.getSecond();
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet resultSet = ps.executeQuery();
            List<Map<String, Object>> dataList = toListMap(resultSet);
            resultSet.close();
            return dataList;
        } finally {

            if (connection != null&&autoClose) {
                connection.close();
            }
        }
    }
    private Long getCount(Pair<String, Connection> pair, String sql,boolean autoClose) throws SQLException {

        Connection connection = null;
        try {
            connection = pair.getSecond();
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet resultSet = ps.executeQuery();
            Long count = null;
            while (resultSet.next()){
                count = resultSet.getLong(1);
            }
            return count;
        } finally {

            if (connection != null&&autoClose) {
                connection.close();
            }
        }
    }


    protected String applyListSql(HmModelPO modelPO, Map<String, Object> params, List<String> orderRules) {
        String columns = getSelectColumns(modelPO);
        String viewName = modelPO.getViewName();
        String whereStr = getWhereStr(params);
        String orderBy = getOrderByStr(orderRules);
        return String.format(LIST_TEMPLATE, columns, viewName, whereStr, orderBy);
    }

    protected String applyPageListSql(HmModelPO modelPO, Map<String, Object> params, List<String> orderRules,Integer pageNo, Integer pageSize) {
        String columns = getSelectColumns(modelPO);
        String viewName = modelPO.getViewName();
        String whereStr = getWhereStr(params);
        String orderBy = getOrderByStr(orderRules);
        int start = PageUtil.getStart(pageNo,pageSize);
        return String.format(LIST_TEMPLATE, columns, viewName, whereStr, orderBy,start,pageSize);
    }

    protected String applyCountSql(HmModelPO hmModelPO,Map<String, Object> params){
        String viewName = hmModelPO.getViewName();
        String whereStr = getWhereStr(params);
        return String.format(COUNT_TEMPLATE,viewName,whereStr);
    }

    @Override
    public PageBase<Map<String, Object>> pageList(HmModelPO modelPO, Map<String, Object> params, List<String> orderRules, Integer pageNo, Integer pageSize) throws SQLException {
       String pageSql = applyPageListSql(modelPO,params,orderRules,pageNo,pageSize);
        Pair<String, Connection> pair = getConnection(modelPO.getDatasourceId());
        List<Map<String,Object>> dataList = getData(pair,pageSql,false);
        Long count = getCount(pair,applyCountSql(modelPO,params),true);
        PageBase<Map<String,Object>> pageBase=new PageBase<>();
        pageBase.setData(dataList);
        pageBase.setTotalCount(count);
        pageBase.setPageNo(pageNo);
        pageBase.setPageSize(pageSize);
        pageBase.setTotalPage(PageUtil.totalPage(count.intValue(),pageSize));
        return pageBase;
    }

    @Override
    public String getSelectColumns(HmModelPO modelPO) {
        List<HmEntityModel> pos = getHmPublishedEntityModelService().getListByModelId(modelPO.getId());
        StringJoiner joiner = new StringJoiner(",");
        pos.forEach(e -> joiner.add(e.getCode()));
        return joiner.toString();
    }

    @Override
    public void afterPropertiesSet() throws Exception {
        getDataBaseType().loadDriver();
    }
}
